package org.hazelnuts;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.*;

public class DataExportTest {
    public static final String URL = "jdbc:oracle:thin:@10.10.10.150:1521:orcl";
    public static final String DRIVER = "oracle.jdbc.OracleDriver";
    public static final String PASSWORD = "wntime";
    public static final String USERNAME = "sleeber";
    private Logger logger = LoggerFactory.getLogger(DataExportTest.class);

    public void exportTest() throws Exception {

        //初始化数据库连接
        Connection connection = initConnection();
        Statement statement = connection.createStatement();

        //FETCH_SIZE 的大小视具体情况而定
        statement.setFetchSize(2000);

        ResultSet resultSet = statement.executeQuery("select a.cmtid,a.cmtscore,b.cmttext\n" +
                "from t_travel_source a\n" +
                "inner join t_chusai_travel b\n" +
                "on a.cmtid = b.cmtid");
        ResultSetMetaData metaData = resultSet.getMetaData();

        long begin = System.currentTimeMillis();
        //数据库每行数据
        StringBuilder rowData = new StringBuilder();

        //单次写入文件的数据(可以删掉)
        String[] pageData = new String[50000];

        //result 为数据库
        int index = 0;
        int num = metaData.getColumnCount();
        while (resultSet.next()) {
            //组装单行数据
            for (int i = 1; i <= num; i++) {
                Object object = resultSet.getObject(i);
                if (null != object) {
                    rowData.append(object.toString());
                }
                rowData.append(",");
            }
            //添加到写文件集合
            pageData[index] = rowData.append("\n").toString();
            //清空行数据
            rowData.setLength(0);
            index++;

            //10W条数据写一次，视情况而定
            if (index % pageData.length == 0) {
                writeFile(pageData, index, "C:\\Users\\Sleeber.Ann\\Desktop\\WNTime\\初赛校对数据.csv");//写数据
                index = 0;
            }
        }

        if (index % pageData.length != 0) {
            writeFile(pageData, index, "C:\\Users\\Sleeber.Ann\\Desktop\\WNTime\\初赛校对数据.csv");//写数据
        }
    }


    /**
     * 初始化数据库连接
     */
    private Connection initConnection() {
        Connection connection = null;

        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            logger.error("error load jdbc driver");
        } catch (SQLException e) {
            logger.error("error get connection");
        }
        return connection;
    }

    /**
     * 写数据到文件
     */
    private void writeFile(String[] data, int length, String path) throws IOException {

        if (null == data) {
            return;
        }

        File file = new File(path);
        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fileOutputStream = new FileOutputStream(path, true);
        OutputStreamWriter write = new OutputStreamWriter(fileOutputStream, "UTF-8");
        BufferedWriter writer = new BufferedWriter(write);
        for (int i = 0; i < length; i++) {
            writer.write(data[i]);
            writer.flush();
            data[i] = null;
        }
        writer.close();
        write.close();
        fileOutputStream.close();
    }

}
